﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Cinser.Common
{

    public static class DataTableExtension
    {
        /// <summary>
        /// 将所有列转换为string
        /// </summary>
        /// <param name="dt"></param>
        public static DataTable ConvertColumnsToString(this DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return dt;
            DataTable dtTemp = dt.Copy();
            dt.Rows.Clear();
            dt.Columns.Clear();
            dt.TableName = dtTemp.TableName;
            for (int i = 0; i < dtTemp.Columns.Count; i++)
            {
                dt.Columns.Add(dtTemp.Columns[i].ColumnName, typeof(string));

            }
            for (int i = 0; i < dtTemp.Rows.Count; i++)
            {
                dt.ImportRow(dtTemp.Rows[i]);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j] != null && dt.Rows[i][j] != DBNull.Value && dt.Rows[i][j].IsDataTime())
                    {
                        dt.Rows[i][j] = dt.Rows[i][j].ToString().Replace("00:00:00", "");
                        dt.Rows[i][j] = dt.Rows[i][j].ToString().Replace("0:00:00", "");
                    }
                }
            }
            return dt;
        }
        public static bool ContainsString(this DataTable dt, string str)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j].ToString().Contains(str))
                    {
                        return true;
                    }
                }
            }
            return false;
        }
        /// <summary>
        /// 增加序号列
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ColumnName"></param>
        /// <returns></returns>
        public static bool AddIDColumn(this DataTable dt, string ColumnName)
        {
            if (!dt.Columns.Contains(ColumnName))
                dt.Columns.Add(ColumnName);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i][ColumnName] = i + 1;
            }
            return false;
        }
        public static bool DeleteRows(this DataTable dt, string ColumnName, string value)
        {
            dt.AcceptChanges();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][ColumnName].ToString().Trim() == value)
                {
                    dt.Rows[i].Delete();
                }
            }
            dt.AcceptChanges();
            return false;
        }
        public static DataTable GetRows(this DataTable dt, int StartIndex, int Count)
        {
            if (dt.Rows.Count <= Count) return dt.Copy();
            else if (dt.Rows.Count < StartIndex + 1) return null;
            DataTable dtRes = new DataTable();
            dtRes.CopyColumns(dt);
            int index = StartIndex;
            for (int i = 0; i < Count; i++)
            {
                if (index >= dt.Rows.Count) break;
                dtRes.ImportRow(dt.Rows[index]);
                index++;
            }
            return dtRes;
        }
        public static void CopyColumns(this DataTable dt, DataTable destDT)
        {
            for (int i = 0; i < destDT.Columns.Count; i++)
            {
                dt.Columns.Add(destDT.Columns[i].ColumnName);
            }
        }
        public static bool IsExist(this DataTable dt, string ColumnName, string value)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][ColumnName].ToString() == value)
                {
                    return true;
                }
                else if (dt.Rows[i][ColumnName].ToString() != value && i == dt.Rows.Count - 1)
                {
                    return false;
                }
            }
            return false;
        }
        public static bool IsExist(this DataTable dt, string[] ColumnName, string[] value)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < ColumnName.Length; j++)
                {
                    if (dt.Rows[i][ColumnName[j]].ToString() != value[j]) break;
                    if (dt.Rows[i][ColumnName[j]].ToString() == value[j] && j == ColumnName.Length - 1) return true;
                }
            }
            return false;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="FixedColumn"></param>
        /// <param name="ColumnName"></param>
        /// <param name="values"></param>
        /// <param name="NeedFZCols">需要赋值的列</param>
        /// <returns></returns>
        public static bool AddRows(this DataTable dt, string FixedColumn, string ColumnName, string[] values, string[] NeedFZCols = null)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < values.Length; j++)
                {
                    if (!dt.IsExist(new string[] { FixedColumn, ColumnName }, new string[] { dt.Rows[i][FixedColumn].ToString(), values[j] }))
                    {
                        DataRow dr = dt.NewRow();
                        dr[FixedColumn] = dt.Rows[i][FixedColumn];
                        dr[ColumnName] = values[j];
                        if (NeedFZCols != null)
                        {
                            for (int k = 0; k < NeedFZCols.Length; k++)
                            {
                                dr[NeedFZCols[k]] = dt.Rows[i][NeedFZCols[k]].ToString();
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            dt.DefaultView.Sort = FixedColumn + " asc," + ColumnName + " asc";
            dt = dt.DefaultView.ToTable();
            return true;
        }

        public static DataRow Copy(this DataRow dr)
        {
            DataRow drRes = dr.Table.NewRow();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                drRes[dr.Table.Columns[i]] = dr[dr.Table.Columns[i]].ToString();
            }
            return drRes;
        }
        public static object ToModel(this DataRow dr, object Model)
        {
            System.Reflection.PropertyInfo[] properties = Model.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            for (int i = 0; i < properties.Length; i++)
            {
                if (dr.Table.Columns.Contains(properties[i].Name))
                {
                    properties[i].SetValue(Model, dr[properties[i].Name], null);
                }
            }
            return Model;
        }
        /// <summary>
        /// 获取某一列的值,用","连起来。
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colName"></param>
        /// <param name="splitchar"></param>
        /// <returns></returns>
        public static string GetColumnValue(this DataTable dt, string colName, string splitchar = ",")
        {
            if (!dt.Columns.Contains(colName)) return "";
            string str = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i != dt.Rows.Count - 1)
                {
                    str = str + dt.Rows[i][colName].ToString() + splitchar;
                }
                else
                {
                    str = str + dt.Rows[i][colName].ToString();

                }
            }
            return str;
        }
        /// <summary>
        /// 将DataTable转换问XML
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="colsWidth">列宽</param>
        /// <returns></returns>
        public static string ToXML(this DataTable dt, params int[] colsWidth)
        {
            StringBuilder strB = new StringBuilder();
            //     strB.Append("<?xml version=\"1.0\"?>");
            strB.Append("<xml>");
            strB.Append("<cols>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                strB.Append("<col>");
                strB.Append("<name>");
                strB.Append(dt.Columns[i].ColumnName);
                strB.Append("</name>");
                strB.Append("<width>");
                if (colsWidth != null && colsWidth.Length > i + 1)
                {
                    strB.Append(colsWidth[i].ToString());
                }
                strB.Append("</width>");
                strB.Append("</col>");
            }
            strB.Append("</cols>");
            strB.Append("<rows>");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strB.Append(string.Format("<row id=\"{0}\">", i.ToString()));
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    strB.Append("<value>");
                    strB.Append(dt.Rows[i][j].ToStringNew());
                    strB.Append("</value>");
                }
                strB.Append("</row>");
            }
            strB.Append("</rows>");
            strB.Append("</xml>");
            return strB.ToString();
        }
        /// <summary>
        /// 按列进行分组
        /// </summary>
        /// <param name="cols"></param>
        /// <returns></returns>
        public static DataSet GroupBy(this DataTable dt, List<string> cols)
        {
            DataSet ds = new DataSet();
            if (cols == null || cols.Count == 0)
            {
                ds.Tables.Add(dt);
            }
            else
            {
                string sortCol = "";
                for (int i = 0; i < cols.Count; i++)
                {
                    if (i == cols.Count - 1)
                        sortCol = sortCol + cols[i] + " asc ";
                    else
                        sortCol = sortCol + cols[i] + ",";
                }
                if (!string.IsNullOrEmpty(dt.DefaultView.Sort))
                    dt.DefaultView.Sort = dt.DefaultView.Sort + "," + sortCol;
                else
                    dt.DefaultView.Sort = sortCol;
                DataTable dtTmp = dt.DefaultView.ToTable();
                DataTable dtRes = null;
                for (int i = 0; i < dtTmp.Rows.Count; i++)
                {
                    if (i == 0)
                    {
                        dtRes = new DataTable();
                        dtRes.CopyColumns(dtTmp);


                        ds.Tables.Add(dtRes);
                        dtRes.ImportRow(dtTmp.Rows[i]);
                    }
                    else
                    {
                        for (int j = 0; j < cols.Count; j++)
                        {
                            if (dtTmp.Rows[i][cols[j]].IsDataTime() && dtTmp.Rows[i][cols[j]].ToDateTime() != dtTmp.Rows[i - 1][cols[j]].ToDateTime())
                            {
                                dtRes = new DataTable();
                                dtRes.CopyColumns(dtTmp);
                                ds.Tables.Add(dtRes);
                                dtRes.ImportRow(dtTmp.Rows[i]);
                                break;
                            }
                            else if (!dtTmp.Rows[i][cols[j]].IsDataTime() && dtTmp.Rows[i][cols[j]].ToString() != dtTmp.Rows[i - 1][cols[j]].ToString())
                            {
                                dtRes = new DataTable();
                                dtRes.CopyColumns(dtTmp);
                                ds.Tables.Add(dtRes);
                                dtRes.ImportRow(dtTmp.Rows[i]);
                                break;
                            }
                            else if (j == cols.Count - 1)
                            {
                                dtRes.ImportRow(dtTmp.Rows[i]);
                            }
                        }
                    }
                }
            }
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                if (ds.Tables[i].Columns.Contains("ID"))
                    ds.Tables[i].AddIDColumn("ID");
            }
            return ds;
        }

        public static string GetSqlWhere(DataRow dr, List<string> keysColumns)
        {
            if (keysColumns.Count > 0)
            {
                string sqlWhere = string.Format(" {0}='{1}'", keysColumns[0], dr[keysColumns[0]].ToString());
                for (int i = 1; i < keysColumns.Count(); i++)
                {
                    sqlWhere += string.Format(" and {0}='{1}'", keysColumns[i], dr[keysColumns[i]].ToString());
                }
                return sqlWhere;
            }
            else
                return " 1=2";
        }
    }
}
